﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.Odbc;

namespace Blizzard.DAL
{
    public class ODBCPlugin : IDAO
    {
        OdbcConnection _cnn = new OdbcConnection();

        public ODBCPlugin(string connectionString)
	    {
            _cnn.ConnectionString = connectionString;
	    }

        public DataTable GetDataTable(string table,string whereClause)
        {
                OdbcCommand cmd = new OdbcCommand( "Select * from " + table + " " + whereClause);
                return getDataTable(cmd);
        }
        public DataTable GetDataTable(string table, string identityFieldName, int id)
        {
            OdbcCommand cmd = new OdbcCommand("Select * From " + table + " where " + identityFieldName + " = " + id);
            return getDataTable(cmd);
        }
        public DataTable GetManyToManyAssociates(string associateTable, string joinTable, string associatedId, string joinTableAssociateId, string joinTableMainId, int mainObjectId)
        {
           

            string sql = "Select A.* From " + associateTable + " A inner join " + joinTable +
                 " J on A." + associatedId + " = J." + joinTableAssociateId +
                 " where " + joinTableMainId + " = " + mainObjectId;

            OdbcCommand cmd = new OdbcCommand(sql);

            return getDataTable(cmd);
        }

        public DataTable GetParent(string associateTable, string mainTable, string associateTableIdField, string associateIdFieldInMainTable, string mainTableIdField, int mainObjectId)
        {
    
          string  sql = "select A.* from " + associateTable + " A inner join " +
                     mainTable + " M on A." + associateTableIdField + " = M." + associateIdFieldInMainTable +
                     " where M." + mainTableIdField + " = " + mainObjectId;


          OdbcCommand cmd = new OdbcCommand(sql);
    
          return getDataTable(cmd);
        }

        public DataTable GetChilderen(string associateTable, string mainTable, string mainTableIdFieldInAssociateTable, string mainTableIdField, int mainObjectId)
        {
           
           string sql = "select A.* from " + associateTable + " A inner join " +
                          mainTable + " M on A." + mainTableIdFieldInAssociateTable + " = M." + mainTableIdField +
                          " where M." + mainTableIdField + " = " + mainObjectId;


           OdbcCommand cmd = new OdbcCommand(sql);

           return getDataTable(cmd);
        }
        
        public void JoinTableDeleter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedOneValue, int fieldTwoValue)
        {

            string sql = " Delete from " + joinTable + " where " +
                    fieldNameOne + " = " + filedOneValue + " and " +
                    fieldNameTwo + " = " + fieldTwoValue;

            OdbcCommand cmd = new OdbcCommand(sql);

            executeNonQuery(cmd);
  
        }
        
        public void DeleteRecord(string tableName, string identityFieldName, int identityFieldValue)
        {
            string sql = " delete from " + tableName + " where " + identityFieldName + " = " + identityFieldValue;

            OdbcCommand cmd = new OdbcCommand(sql);
       
            executeNonQuery(cmd);
        }
        public void Insert(string tableName, Dictionary<string, object> fieldValuePairs)
        {

            OdbcCommand cmd = new OdbcCommand();
      
            string sql = "INSERT INTO " + tableName + " ( ";

            foreach (KeyValuePair<string,object> fieldValue in fieldValuePairs)
            {
                
                
                string field = fieldValue.Key;

                object propertyValue = fieldValue.Value;

                cmd.Parameters.Add(new OdbcParameter("?", propertyValue));
                sql = sql + field + ",";
           
            }

            sql = sql.TrimEnd(',');
            sql = sql + ") VALUES (";

            foreach (OdbcParameter parameter in cmd.Parameters)
            {
                sql = sql + parameter + ",";
            }

            sql = sql.TrimEnd(',');
            sql = sql + ")";


            cmd.CommandText = sql;
            executeNonQuery(cmd);
            
        }
        
        public int GetTheLastInsertedId(string tableName, string identityField)
        {
            OdbcCommand cmd = new OdbcCommand("Select MAX(" + identityField + ") From " + tableName); 
            return (int)executeScalar(cmd);
        }
  
        public void Update(string tableName, string identityFieldName, int identityFieldValue, Dictionary<string, object> fieldValuePairs)
        {


            OdbcCommand cmd = new OdbcCommand();
        
            string sql = "Update " + tableName + " set ";
            
            foreach (KeyValuePair<string, object> fieldValue in fieldValuePairs)
            {
                string field = fieldValue.Key;
                object propertyValue = fieldValue.Value;

               

                OdbcParameter parameter = new OdbcParameter("?", propertyValue);
                cmd.Parameters.Add(parameter);
                sql = sql + field + " = " + parameter + ",";
             }

            sql = sql.TrimEnd(',');
            sql = sql + " where " + identityFieldName + " = " + identityFieldValue;

            cmd.CommandText = sql;
            
            executeNonQuery(cmd);

        }
    
        public void JoinTableInserter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedValueOne, int fieldValueTwo)
        {
            OdbcCommand cmd = new OdbcCommand();



            OdbcParameter parameter1 = new OdbcParameter("?", filedValueOne);
            OdbcParameter parameter2 = new OdbcParameter("?", fieldValueTwo);

            cmd.Parameters.Add(parameter1);
            cmd.Parameters.Add(parameter2);


            string sql = "Insert into " + joinTable + " ( " + fieldNameOne +
                " , " + fieldNameTwo + " ) Values ( " + parameter1 + " , " + parameter2 + " ) ";

            cmd.CommandText = sql;
            
            executeNonQuery(cmd);
        }
      
        public void SetParentId(string childTable, string childIdField, int childId, string parentIdFieldInChildTable, int parentIdValue)
        {
            string sql = " Update " + childTable + " set " + parentIdFieldInChildTable + " = " + parentIdValue + "  where " +
             childIdField + " = " + childId;

            OdbcCommand cmd = new OdbcCommand(sql);
       
            executeNonQuery(cmd);
        }

        //===============================================private methods==================================================
        //===============================================private methods==================================================
        //===============================================private methods==================================================
        //===============================================private methods==================================================

        private void executeNonQuery(OdbcCommand cmd)
        {
            cmd.Connection = _cnn;

            if (_cnn.State != ConnectionState.Open)
                _cnn.Open();

            cmd.ExecuteNonQuery();
        }
        private object executeScalar(OdbcCommand cmd)
        {
            cmd.Connection = _cnn;

            if (_cnn.State != ConnectionState.Open)
                _cnn.Open();

            return cmd.ExecuteScalar();
        }
        private DataTable getDataTable(OdbcCommand cmd)
        {
            DataTable dt = new DataTable();

            if (_cnn.State != ConnectionState.Open)
                _cnn.Open();

            cmd.Connection = _cnn;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);

            da.Fill(dt);

            return dt;
        }

    }
}
